﻿CREATE Trigger tr_Loan_Verification_Update
On dbo.t_Loan_Verification
For Update
As


IF @@ROWCOUNT <> 1 RETURN

Declare @LoanID int, @VerID int, @Checked bit,@IsCustom varchar,@Lable varchar(1024),
				@IsOwner bit, @IsParent bit,@CommentToSelf nvarchar(4000),
				@CommentToVerifierHist varchar(8000),@CommentToVerifier varchar(8000)

select @LoanID = i.LoanID,@VerID = i.VerificationID,@IsCustom = i.IsCustom,@Lable = i.Lable,
			 @IsOwner = CASE WHEN i.Owner = dbo.UserName() OR dbo.UserLike('Admin') = 1 THEN 1 ELSE 0 END,
			 @Checked = i.Checked, @IsParent = LV.IsParent,@CommentToSelf = i.CommentToSelf,
			 @CommentToVerifierHist = i.CommentToVerifierHist,@CommentToVerifier = i.CommentToVerifier
from inserted i INNER JOIN v_Loan_Verification LV ON i.LoanID = LV.LoanID AND i.VerificationID = LV.ID

IF ISNULL(@CommentToSelf,'') > '' AND @Checked = 1 BEGIN
	RAISERROR('Clear the ToDo message first.',16,1)
	ROLLBACK TRAN
	RETURN
END


IF UPDATE(CommentToVerifier) AND NOT UPDATE(CommentToVerifierHist)
	UPDATE t_Loan_Verification
	SET CommentToVerifierHist = '***** '+dbo.UserName()+' '+CONVERT(varchar,getdate())+' *****
'+lv.CommentToVerifier+ISNULL('
'+i.CommentToVerifierHist,''),
			CommentToVerifier = NULL
	FROM t_Loan_Verification lv INNER JOIN inserted i ON lv.LoanID = i.LoanID AND i.VerificationID = lv.VerificationID
	WHERE i.CommentToVerifier > ''

IF UPDATE(CommentToSelf) BEGIN
	UPDATE t_Loan_Verification
	SET Checked = 0
	FROM t_Loan_Verification lv INNER JOIN inserted i ON lv.LoanID = i.LoanID AND i.VerificationID = lv.VerificationID
	WHERE LTRIM(RTRIM(i.CommentToSelf)) > '' AND i.Checked = 1
END

IF UPDATE (CommentToManager) 
	UPDATE t_Loan_Verification
	SET CommentToManager_Date = GETDATE()
	FROM t_Loan_Verification lv INNER JOIN inserted i ON lv.LoanID = i.LoanID AND i.VerificationID = lv.VerificationID

-- Done
IF UPDATE(Checked)
	IF @Lable <> 'Done' AND
		 @IsCustom = 1 AND
		 @IsOwner = 0 AND
		 @Checked = 1
	BEGIN

		ROLLBACK TRAN
		SELECT ID, Checked FROM  fverTree_Parents(@LoanID,@VerID)

		IF NOT EXISTS(SELECT * FROM v_Loan_Verification
									WHERE LoanID = @LoanID AND ParentID = @VerID AND Lable = 'Done')
		BEGIN -- Start Show Done item

			DECLARE @DoneID int
			
			INSERT INTO t_Loan_Verification(LoanID,ParentID,Lable,IsCustom,Checked)
			VALUES(@LoanID,@VerID,'Done',1,1)
			
			SELECT @DoneID = VerificationID FROM t_Loan_Verification WHERE LoanID = @LoanID AND ParentID = @VerID AND Lable = 'Done'

			SELECT * FROM VerificationTree_Sec(@LoanID,@DoneID)

		END -- End Show Done item
	END
	ELSE BEGIN

	SELECT ID, Checked FROM  fverTree_Parents(@LoanID,@VerID)

	END

IF TRIGGER_NESTLEVEL(@@PROCID) = 1
	UPDATE t_Loan_Verification SET DateStamp = GETDATE()
	FROM t_Loan_Verification lv
	INNER JOIN inserted i ON lv.LoanID = i.LoanID AND i.VerificationID = lv.VerificationID




